package com.pentofab.payrollmanagement.database.impl;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import com.pentofab.payrollmanagement.dao.model.BankInfo;
import com.pentofab.payrollmanagement.dao.model.Company;
import com.pentofab.payrollmanagement.dao.model.Department;
import com.pentofab.payrollmanagement.dao.model.Designation;
import com.pentofab.payrollmanagement.dao.model.EmpCategory;
import com.pentofab.payrollmanagement.dao.model.Employee;
import com.pentofab.payrollmanagement.dao.model.Units;
import com.pentofab.payrollmanagement.database.EmployeeRepository;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Company;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Department;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Designation;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Emp_Category;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Employee;
import com.pentofab.payrollmanagement.database.impl.EmployeeTableSQLConstants.Table_Unit;
import com.pentofab.payrollmanagement.exception.BusinessException;
import com.pentofab.payrollmanagement.exception.DatabaseException;
import com.pentofab.payrollmanagement.utilities.Constants;
import com.pentofab.payrollmanagement.utilities.PayRollUtility;

public class EmployeeRepositoryImpl implements EmployeeRepository {

	Connection conn = null;

	public EmployeeRepositoryImpl() {

		conn = CreateDBConnection.cteateConnectionObj();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.PayRoll.PayRollRepository.EmployeeRepository#getAllEmployeeInfo()
	 */
	public ArrayList<Employee> getAllEmployeeInfo() {
		ArrayList<Employee> employeList = new ArrayList<Employee>();
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.PayRoll.PayRollRepository.PayRollRepository#getEmpById(java.lang.
	 * String)
	 */
	public Employee getEmpById(String empId) throws Exception {
		Employee employee = null;
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.empByIDQery);
		System.out.println(EmployeeTableSQLConstants.empByIDQery);
		preparedStatement.setString(1, empId);
		preparedStatement.setMaxRows(1);
		ResultSet rs = preparedStatement.executeQuery();
		if (!rs.isBeforeFirst()) {
			throw new BusinessException(NO_EMP_FOUND_FOR_ID + empId);
		}
		while (rs.next()) {

			employee = new Employee();
			employee.setEmpId(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.empId));
			employee.setEmpName(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.empName));
			employee.setTelophone1(rs.getString(Table_Employee.telophone1));
			employee.setTelophone2(rs.getString(Table_Employee.telophone2));
			employee.setAddress1(rs.getString(Table_Employee.address1));
			employee.setAddress2(rs.getString(Table_Employee.address2));
			employee.setCity(rs.getString(Table_Employee.city));
			employee.setState(rs.getString(Table_Employee.state));
			employee.setCountry(rs.getString(Table_Employee.country));
			employee.setZipCode(rs.getString(Table_Employee.zipCode));
			employee.setDOJ(rs.getString(Table_Employee.DOJ));
			employee.setHoursperday(rs.getString(Table_Employee.hoursperday));
			employee.setSex(rs.getString(Table_Employee.sex));

			String departmentCode = rs.getString(Table_Employee.deptCode);
			String companyCode = rs.getString(Table_Employee.CompanyCode);
			Department department = getDepartment( companyCode, departmentCode);
			employee.setDepertment(department);

			String designationCode = rs
					.getString(Table_Employee.designationCode);
			Designation designation = getDesignation(companyCode,designationCode);
			employee.setDesignation(designation);

			String empCategoryCode = rs.getString(Table_Employee.Category);
			EmpCategory category = getEmpCategory(companyCode, empCategoryCode);
			employee.setCategory(category);
			Company company = getCompany(companyCode);
			employee.setCompany(company);
			
			employee.setWeeklyOff(rs.getString(EmployeeTableSQLConstants.Table_Employee.weeklyoff));
			employee.setCreatedby(rs.getString(EmployeeTableSQLConstants.Table_Employee.createdby));
			employee.setModificaitonDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.modificaitonDate));
			employee.setModifiedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.modifiedBy));
			employee.setDeletedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletedBy));
			employee.setDeletionDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletionDate));
			employee.setDOB(rs.getString(EmployeeTableSQLConstants.Table_Employee.DOB));
			employee.setUnit(rs.getString(EmployeeTableSQLConstants.Table_Employee.unitCode));
			int rools = rs.getInt(Table_Employee.rools);
			if (rools == 1) {
				employee.setRools(true);
			} else {
				employee.setRools(false);
			}

		
			
		}

		return employee;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.PayRoll.PayRollRepository.PayRollRepository#getEmpByName(java.lang
	 * .String)
	 */
	@Override
	public ArrayList<Employee> getEmpByName(String companyCode, String empName,
			String operation) throws Exception {
		ArrayList<Employee> employeeList = new ArrayList<Employee>();

		String query = null;

		if (operation.equals(Constants.like)) {
			query = EmployeeTableSQLConstants.getAllEmpByNameQuery;
			empName = empName.toLowerCase() + "%";
		} else {
			query = EmployeeTableSQLConstants.exactMatchEmpByNameQuery;
			empName = empName.toLowerCase();
		}
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		PayRollUtility.debug(preparedStatement.toString());
		preparedStatement.setString(1, empName);
		System.out.println("preparedStatement : " + preparedStatement);
		ResultSet rs = preparedStatement.executeQuery();

		if (!rs.isBeforeFirst()) {
			throw new BusinessException(NO_EMP_INFO_FOUND + empName);
		}
		while (rs.next()) {
			Employee employee = new Employee();
			employee.setEmpId(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.empId));
			employee.setEmpName(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.empName));

			// employee.setAddress(rs
			// .getString(EmployeeTableSQLConstants.Table_Employee.Address));

			EmpCategory category = new EmpCategory();
			category.setCode(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.Category));
			employee.setCategory(category);

			Company company = new Company();
			company.setCompanyID(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.CompanyCode));
			employee.setCompany(company);

			Department depertment = new Department();
			depertment
					.setDescription(rs
							.getString(EmployeeTableSQLConstants.Table_Employee.deptCode));
			employee.setDepertment(depertment);

			Designation designation = new Designation();
			designation
					.setCode(rs
							.getString(EmployeeTableSQLConstants.Table_Employee.designationCode));
			employee.setDesignation(designation);

			employee.setDOJ(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.DOJ));
			employee.setHoursperday(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.hoursperday));

			employee.setRools(rs
					.getBoolean(EmployeeTableSQLConstants.Table_Employee.rools));
			employee.setSex(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.sex));
			employee.setTelophone1(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.telophone1));
			employee.setTelophone2(rs
					.getString(EmployeeTableSQLConstants.Table_Employee.telophone2));
			employee.setWeeklyOff(rs.getString(EmployeeTableSQLConstants.Table_Employee.weeklyoff));
			employee.setCreatedby(rs.getString(EmployeeTableSQLConstants.Table_Employee.createdby));
			employee.setModificaitonDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.modificaitonDate));
			employee.setModifiedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.modifiedBy));
			employee.setDeletedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletedBy));
			employee.setDeletionDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletionDate));
			employee.setDOB(rs.getString(EmployeeTableSQLConstants.Table_Employee.DOB));
			employee.setUnit(rs.getString(EmployeeTableSQLConstants.Table_Employee.unitCode));
			employeeList.add(employee);
			

		}
		return employeeList;
	}

	@Override
	public void addEmploye(Employee employee,boolean updateNextEmpNo) throws Exception {
		// Just fowwol addAttandace mathod
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.createEmployee);
		
		System.out.println("employee ; "+employee);
		
		PayRollUtility.debug("preparedStatement : "+preparedStatement);
		preparedStatement.setString(1, employee.getEmpId());
		preparedStatement.setString(2, employee.getEmpName());
		preparedStatement.setString(3, employee.getDOB());
		preparedStatement.setString(4, employee.getTelophone1());
		preparedStatement.setString(5, employee.getTelophone2());
		preparedStatement.setString(6, employee.getAddress1());
		preparedStatement.setString(7, employee.getAddress2());
		preparedStatement.setString(8, employee.getCity());
		preparedStatement.setString(9, employee.getState());
		preparedStatement.setString(10, employee.getCountry());
		preparedStatement.setString(11, employee.getZipCode());
		preparedStatement.setString(12, employee.getCompany().getCompanyID());
		preparedStatement.setString(13, employee.getSex());
		preparedStatement.setString(14, employee.getDOJ());
		preparedStatement.setString(15, employee.getDesignation().getCode());
		preparedStatement.setString(16, employee.getDepertment().getCode());
		preparedStatement.setString(17, employee.getCategory().getCode());
		preparedStatement.setBoolean(18, employee.isRools());
		preparedStatement.setString(19, employee.getHoursperday());
		preparedStatement.setString(20, employee.getWeeklyOff());
		preparedStatement.setString(21, employee.getCreatedby());
		preparedStatement.setString(22, employee.getUnit());

		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("createEmployee status : " + status);
			
			
			if(status > 0 && updateNextEmpNo){
				preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.setNextEmpNo);
				preparedStatement.setString(1, employee.getCompany().getCompanyID());
				PayRollUtility.debug("preparedStatement update next emp id : "+preparedStatement);
				preparedStatement.executeUpdate();
			}
			
		} catch (MySQLIntegrityConstraintViolationException exception) {
			throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
		} catch (Exception e) {
			e.printStackTrace();
			throw new DatabaseException(0);
		}
	}

	@Override
	public void updateEmployee(Employee employee) throws Exception {
		System.out.println("updateEmployee : updateEmployee : ");
		PreparedStatement preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.updateEmployee);

		preparedStatement.setString(1, employee.getEmpName());
		preparedStatement.setString(2, employee.getDOB());
		preparedStatement.setString(3, employee.getTelophone1());
		preparedStatement.setString(4, employee.getTelophone2());
		preparedStatement.setString(5, employee.getAddress1());
		preparedStatement.setString(6, employee.getAddress2());
		preparedStatement.setString(7, employee.getCity());
		preparedStatement.setString(8, employee.getState());
		preparedStatement.setString(9, employee.getCountry());
		preparedStatement.setString(10, employee.getZipCode());
		preparedStatement.setString(11, employee.getCompany().getCompanyID());
		preparedStatement.setString(12, employee.getSex());
		preparedStatement.setString(13, employee.getDOJ());
		preparedStatement.setString(14, employee.getDesignation().getCode());
		preparedStatement.setString(15, employee.getDepertment().getCode());
		preparedStatement.setString(16, employee.getCategory().getCode());
		preparedStatement.setBoolean(17, employee.isRools());
		preparedStatement.setString(18, employee.getHoursperday());
		preparedStatement.setString(19, employee.getWeeklyOff());
		preparedStatement.setString(20, employee.getModifiedBy());
		preparedStatement.setString(21, employee.getUnit());
		preparedStatement.setString(22, employee.getEmpId());
		PayRollUtility.debug("Upldate employee : "+preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("update Employee status : " + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public void deleteEmployee(String employeeId, String userName) throws Exception {
		
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.deleteEmployee);
		preparedStatement.setString(1, userName);
		preparedStatement.setString(2, employeeId);
		
		PayRollUtility.debug("preparedStatement : "+preparedStatement);
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("update Employee status : " + status);
	}
	
	public String getNextEmpId(String companyCode) throws Exception{    
		try {
			PreparedStatement preparedStatement = conn
					.prepareStatement(EmployeeTableSQLConstants.nextEmpId);
			preparedStatement.setString(1,companyCode );
			ResultSet rs = preparedStatement.executeQuery();

			if(!rs.isBeforeFirst()){
				throw new Exception(CONFIGURATION_NEXT_EMP_ID_NOT_SET);
			}
			if(rs.next())
				return rs.getString(EmployeeTableSQLConstants.NextEmpSequence);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public ArrayList<Employee> getAllCompanyEmployees(String companyCode)
			throws Exception {
		ArrayList<Employee> employeeList = new ArrayList<Employee>();

		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllEmpByCompany);
		PayRollUtility.debug(preparedStatement.toString());
		preparedStatement.setString(1, companyCode);
		System.out.println("preparedStatement : " + preparedStatement);
		ResultSet rs = preparedStatement.executeQuery();

		if (!rs.isBeforeFirst()) {
			return null;
		}
		while (rs.next()) {
			Employee employee = new Employee();
			employee.setEmpId(rs.getString(EmployeeTableSQLConstants.Table_Employee.empId));
			employee.setEmpName(rs.getString(EmployeeTableSQLConstants.Table_Employee.empName));
			employee.setTelophone1(rs.getString(Table_Employee.telophone1));
			employee.setTelophone2(rs.getString(Table_Employee.telophone2));
			employee.setAddress1(rs.getString(Table_Employee.address1));
			employee.setAddress2(rs.getString(Table_Employee.address2));
			employee.setCity(rs.getString(Table_Employee.city));
			employee.setState(rs.getString(Table_Employee.state));
			employee.setCountry(rs.getString(Table_Employee.country));
			employee.setZipCode(rs.getString(Table_Employee.zipCode));
			employee.setDOJ(rs.getString(Table_Employee.DOJ));
			employee.setHoursperday(rs.getString(Table_Employee.hoursperday));
			employee.setSex(rs.getString(Table_Employee.sex));
			employee.setUnit(rs.getString(EmployeeTableSQLConstants.Table_Employee.unitCode));

			String departmentCode = rs.getString(Table_Employee.deptCode);
			Department department = getDepartment(companyCode, departmentCode);
			employee.setDepertment(department);

			String designationCode = rs
					.getString(Table_Employee.designationCode);
			Designation designation = getDesignation(companyCode,
					designationCode);
			employee.setDesignation(designation);

			String empCategoryCode = rs.getString(Table_Employee.Category);
			EmpCategory category = getEmpCategory(companyCode, empCategoryCode);
			employee.setCategory(category);
			Company company = getCompany(companyCode);
			employee.setCompany(company);
			
			employee.setWeeklyOff(rs.getString(EmployeeTableSQLConstants.Table_Employee.weeklyoff));
			employee.setCreatedby(rs.getString(EmployeeTableSQLConstants.Table_Employee.createdby));
			employee.setModificaitonDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.modificaitonDate));
			employee.setModifiedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.modifiedBy));
			employee.setDeletedBy(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletedBy));
			employee.setDeletionDate(rs.getString(EmployeeTableSQLConstants.Table_Employee.deletionDate));
			employee.setDOB(rs.getString(EmployeeTableSQLConstants.Table_Employee.DOB));
			int rools = rs.getInt(Table_Employee.rools);
			if (rools == 1) {
				employee.setRools(true);
			} else {
				employee.setRools(false);
			}

			employeeList.add(employee);
		}
		
		System.out.println("employeeList '; "+employeeList);
		return employeeList;

	}

	@Override
	public void addDepartment(Department department) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.addDepartment);

		preparedStatement.setString(1, department.getCompanyCode());
		preparedStatement.setString(2, department.getCode());
		preparedStatement.setString(3, department.getDescription());

		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("create Department status : " + status);
		} catch (MySQLIntegrityConstraintViolationException exception) {
			throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public Department getDepartment(String companyCode, String code)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getDepartment);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);

		try {
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				return null;
			}

			if (rs.next()) {
				Department dept = new Department();
				dept.setCompanyCode(rs.getString(Table_Department.COMPANY_CODE));
				dept.setCode(rs.getString(Table_Department.CODE));
				dept.setDescription(rs.getString(Table_Department.DESC));
				return dept;
			} else {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public void deleteDepartment(String companyCode, String code)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.deleteDepartment);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of delete :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void updateDepartment(Department department) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.updateDepartment);
		preparedStatement.setString(1, department.getDescription());
		preparedStatement.setString(2, department.getCompanyCode());
		preparedStatement.setString(3, department.getCode());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of update :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public List<Department> getAllDepartments(String companyCode)
			throws Exception {

		ArrayList<Department> departments = new ArrayList<Department>();
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllDepartments);
		preparedStatement.setString(1, companyCode);

			System.out.println("preparedStatement ; "+preparedStatement);
		try {
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				return departments;
			}
			while (rs.next()) {
				Department dept = new Department();
				dept.setCompanyCode(rs.getString(Table_Department.COMPANY_CODE));
				dept.setCode(rs.getString(Table_Department.CODE));
				dept.setDescription(rs.getString(Table_Department.DESC));
				departments.add(dept);
			}
			return departments;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void addDesignation(Designation design) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.addDesignation);
		preparedStatement.setString(1, design.getCompanyCode());
		preparedStatement.setString(2, design.getCode());
		preparedStatement.setString(3, design.getDescription());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("create Designation status : " + status);
		} catch (MySQLIntegrityConstraintViolationException exception) {
			throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public Designation getDesignation(String companyCode, String code)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getDesignation);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);

		try {
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}

			if (rs.next()) {
				Designation desgn = new Designation();
				desgn.setCompanyCode(rs
						.getString(Table_Designation.COMPANY_CODE));
				desgn.setCode(rs.getString(Table_Designation.CODE));
				desgn.setDescription(rs.getString(Table_Designation.DESC));
				return desgn;
			} else {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public List<Designation> getAllDesignation(String companyCode)
			throws Exception {
		
		ArrayList<Designation> designations = new ArrayList<Designation>();
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllDesignations);
		preparedStatement.setString(1, companyCode);
		try {
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				return designations;
			}
			while (rs.next()) {
				Designation desgn = new Designation();
				desgn.setCompanyCode(rs
						.getString(Table_Designation.COMPANY_CODE));
				desgn.setCode(rs.getString(Table_Designation.CODE));
				desgn.setDescription(rs.getString(Table_Designation.DESC));
				designations.add(desgn);
			}
			return designations;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public void deleteDesignation(String companyCode, String code)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.deleteDesignation);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of delete :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void updateDesignation(Designation design) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.updateDesignation);
		preparedStatement.setString(1, design.getDescription());
		preparedStatement.setString(2, design.getCompanyCode());
		preparedStatement.setString(3, design.getCode());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of update :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void addEmpCategory(EmpCategory cateogry) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.addCategory);
		preparedStatement.setString(1, cateogry.getCompanyCode());
		preparedStatement.setString(2, cateogry.getCode());
		preparedStatement.setString(3, cateogry.getDescription());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("create EmpCagtegory status : " + status);
		} catch (MySQLIntegrityConstraintViolationException exception) {
			throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}

	}

	@Override
	public EmpCategory getEmpCategory(String companyCode, String code)
			throws Exception {

		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getCategory);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);

		try {
			ResultSet rs = preparedStatement.executeQuery();
			if (!rs.isBeforeFirst()) {
				return null;
			}

			if (rs.next()) {
				EmpCategory empCategory = new EmpCategory();
				empCategory.setCompanyCode(rs.getString(Table_Emp_Category.COMPANY_CODE));
				empCategory.setCode(rs.getString(Table_Emp_Category.CODE));
				empCategory.setDescription(rs.getString(Table_Emp_Category.DESC));
				return empCategory;
			} else {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public List<EmpCategory> getAllEmpCategory(String companyCode)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllCategory);
		preparedStatement.setString(1, companyCode);

		ArrayList<EmpCategory> categories = new ArrayList<EmpCategory>();
		try {
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				return categories;
			}
			while (rs.next()) {
				EmpCategory empCategory = new EmpCategory();
				empCategory.setCode(rs.getString(Table_Emp_Category.CODE));
				empCategory.setDescription(rs
						.getString(Table_Emp_Category.DESC));
				categories.add(empCategory);
			}
			return categories;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void deleteEmpCategory(String companyCode, String code)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.deleteCategory);
		preparedStatement.setString(1, companyCode);
		preparedStatement.setString(2, code);
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of delete :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void updateEmpCategory(EmpCategory empCategory) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.updateCategory);
		preparedStatement.setString(1, empCategory.getDescription());
		preparedStatement.setString(2, empCategory.getCompanyCode());
		preparedStatement.setString(3, empCategory.getCode());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of update :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void addCompany(Company company) throws Exception {
		try {
			PreparedStatement preparedStatement = conn
					.prepareStatement(EmployeeTableSQLConstants.createCompany);
			preparedStatement.setString(1, company.getCompanyID());
			preparedStatement.setString(2, company.getName());
			preparedStatement.setString(3, company.getAddress1());
			preparedStatement.setString(4, company.getAddress2());
			preparedStatement.setString(5, company.getCity());
			preparedStatement.setString(6, company.getState());
			preparedStatement.setString(7, company.getCountry());
			preparedStatement.setString(8, company.getZipcode());
			preparedStatement.setString(9, company.getTelephone1());
			preparedStatement.setString(10, company.getTelephone2());
			preparedStatement.setString(11, company.getFax());
			preparedStatement.setString(12, company.getMailId());
			PayRollUtility.debug(preparedStatement.toString());
			try {
				int status = preparedStatement.executeUpdate();
				PayRollUtility.debug("create company status : " + status);
				
				preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.setEmployeeSequence);
				preparedStatement.setString(1, company.getCompanyID());
				preparedStatement.executeUpdate();
			} catch (MySQLIntegrityConstraintViolationException exception) {
				exception.printStackTrace();
				throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
			} catch (Exception e) {
				e.printStackTrace();
				throw new DatabaseException(0);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public List<Company> getCompanies() throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllCompanies);
		List<Company> companies = new ArrayList<Company>();
		try {
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				return companies;
			}
			while (rs.next()) {
				Company company = new Company();
				company.setName(rs.getString(Table_Company.name));
				company.setCompanyID(rs.getString(Table_Company.companyCode));
				company.setAddress1(rs.getString(Table_Company.address1));
				company.setAddress2(rs.getString(Table_Company.address2));
				company.setCity(rs.getString(Table_Company.city));
				company.setState(rs.getString(Table_Company.state));
				company.setCountry(rs.getString(Table_Company.country));
				company.setZipcode(rs.getString(Table_Company.zipcode));
				company.setTelephone1(rs.getString(Table_Company.telophone1));
				company.setTelephone2(rs.getString(Table_Company.telephone2));
				company.setFax(rs.getString(Table_Company.fax));
				company.setMailId(rs.getString(Table_Company.emalId));
				companies.add(company);
			}
			return companies;

		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	public Company getCompany(String companyCode) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getCompanyById);
		preparedStatement.setString(1, companyCode);
		try {
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}
			if (rs.next()) {
				Company company = new Company();
				company.setName(rs.getString(Table_Company.name));
				company.setCompanyID(rs.getString(Table_Company.companyCode));
				company.setAddress1(rs.getString(Table_Company.address1));
				company.setAddress2(rs.getString(Table_Company.address2));
				company.setCity(rs.getString(Table_Company.city));
				company.setState(rs.getString(Table_Company.state));
				company.setCountry(rs.getString(Table_Company.country));
				company.setZipcode(rs.getString(Table_Company.zipcode));
				company.setTelephone1(rs.getString(Table_Company.telophone1));
				company.setTelephone2(rs.getString(Table_Company.telephone2));
				company.setFax(rs.getString(Table_Company.fax));
				company.setMailId(rs.getString(Table_Company.emalId));
				return company;
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
		return null;
	}
	
	
	public Units getUnit(String unitCode, String compCode) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getUnitById);
		preparedStatement.setString(1, unitCode);
		preparedStatement.setString(2, compCode);
		try {
			PayRollUtility.debug("getUnit : "+preparedStatement);
			ResultSet rs = preparedStatement.executeQuery();

			if (!rs.isBeforeFirst()) {
				throw new DatabaseException(DatabaseException.NO_ENTRY_FOUND);
			}
			if (rs.next()) {
				Units unit = new Units();
				unit.setName(rs.getString(Table_Unit.name));
				unit.setUnitId(rs.getString(Table_Unit.unitCode));
				unit.setCompanyCode(rs.getString(Table_Unit.companyCode));
				unit.setAddress1(rs.getString(Table_Unit.address1));
				unit.setAddress2(rs.getString(Table_Unit.address2));
				unit.setCity(rs.getString(Table_Unit.city));
				unit.setState(rs.getString(Table_Unit.state));
				unit.setCountry(rs.getString(Table_Unit.country));
				unit.setZipcode(rs.getString(Table_Unit.zipcode));
				unit.setTelephone1(rs.getString(Table_Unit.telophone1));
				unit.setTelephone2(rs.getString(Table_Unit.telephone2));
				unit.setFax(rs.getString(Table_Unit.fax));
				unit.setMailId(rs.getString(Table_Unit.emalId));
				return unit;
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			e.printStackTrace();
			throw new DatabaseException(0);
		}
		return null;
	}
	
	public void updateCompany(Company company) throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.updateCompany);
		preparedStatement.setString(1, company.getName());
		preparedStatement.setString(2, company.getAddress1());
		preparedStatement.setString(3, company.getAddress2());
		preparedStatement.setString(4, company.getCity());
		preparedStatement.setString(5, company.getState());
		preparedStatement.setString(6, company.getCountry());
		preparedStatement.setString(7, company.getZipcode());
		preparedStatement.setString(8, company.getTelephone1());
		preparedStatement.setString(9, company.getTelephone2());
		preparedStatement.setString(10, company.getFax());
		preparedStatement.setString(11, company.getMailId());
		preparedStatement.setString(12, company.getCompanyID());
		PayRollUtility.debug(preparedStatement.toString());
		try {
			int status = preparedStatement.executeUpdate();
			PayRollUtility.debug("Status of update :" + status);
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}
	
	
	public List<String> getTableNames() throws Exception{
		
		ArrayList<String> currentTable = new  ArrayList<String>();
		PreparedStatement preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.tableNameQuery);
		ResultSet rs = preparedStatement.executeQuery();
		while(rs.next())
		{
			if( rs.getString(1) != null)
			currentTable.add( rs.getString(1));
				 
		}
		return currentTable;

	}
	
	public String getColumnNames(String tableName) throws Exception{
		
		PreparedStatement preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.getColumsQuery);
		preparedStatement.setString(1, tableName);
		
		ResultSet rs = preparedStatement.executeQuery();
		StringBuffer colNmae= new StringBuffer();
		while(rs.next())
		{
			colNmae.append(rs.getString(1)+",");
		}
		return colNmae.substring(0,colNmae.lastIndexOf(","));

	}
	
	
	public static void writeData(){
		
		EmployeeRepositoryImpl e = new EmployeeRepositoryImpl();
		try
		{

			List<String> tableNameList = 	e.getTableNames();
			Iterator<String> tableNameIterator = tableNameList.listIterator();
		
			while(tableNameIterator.hasNext()){
				
				String tableName= tableNameIterator.next();
				String colNames = e.getColumnNames(tableName);
				String[] colNamesArray = e.getColumnNames(tableName).split(",");
				String selQuery = "Select "+colNames+" from "+tableName;
				
				
				FileWriter file =new FileWriter("E://"+tableName+".csv");
				BufferedWriter bwr = new BufferedWriter(file);
				PreparedStatement dataStatement =e.conn.prepareStatement(selQuery);
				bwr.write(e.getColumnNames(tableName));
				ResultSet rs = dataStatement.executeQuery();
				while(rs.next())
				{
					StringBuffer data =new StringBuffer();
					for(int i = 0;i < colNamesArray.length;i++)
					{
						data.append("'"+rs.getString(colNamesArray[i])+"'");
						if(i < colNamesArray.length-1)
						{
							data.append(",");
						}
					}
				bwr.write("\n");
				bwr.write(data.toString());
				}
				bwr.flush();
				file.flush();
				bwr.close();
				file.close();
				
				PayRollUtility.encrypt("santhosh123", "E://"+tableName+".csv", "E://"+tableName+"_ENC.csv");
			}
		} catch (Throwable e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		
		
	}
	
	public static void readAndUploadData() throws Exception
	{
		EmployeeRepositoryImpl e = new EmployeeRepositoryImpl();
		try
		{

			List<String> tableNameList = 	e.getTableNames();
			Iterator<String> tableNameIterator = tableNameList.listIterator();
		
			while(tableNameIterator.hasNext())
			{
				boolean first = true;
				
				
 				String strLine;
				String tableName= tableNameIterator.next();
				PayRollUtility.decrypt("santhosh123", "E://"+tableName+"_ENC.csv", "G://"+tableName+".csv");
				
				
				String colNames = e.getColumnNames(tableName);
				BufferedReader br = new BufferedReader(new FileReader("G://"+tableName+".csv"));
                Iterator itr;
                while ((strLine = br.readLine()) != null) 
                {
                	if(first){

                		first =false;
                		continue;
                	}
                	
                	String sqlBfrQuery = "SET FOREIGN_KEY_CHECKS=0";
            		PreparedStatement dataBfrStatement =e.conn.prepareStatement(sqlBfrQuery);
            		dataBfrStatement.executeUpdate();
                	String[] dataArray = strLine.split(",");
                	System.out.println(tableName +" : dataArray : "+dataArray.length);
                	
                	String sqlQuery = "Insert into " +tableName+ "("+colNames+" ) values (" +strLine+ " )";
                	System.out.println(sqlQuery);
                	PreparedStatement dataStatement =e.conn.prepareStatement(sqlQuery);
                	 dataStatement.executeUpdate();
                	
                }
			}
			
		} catch (Throwable e1) {
			System.out.println(" SQL Exception");
			e1.printStackTrace();
		}
				
					
	}
	
	public static void writeData(String empId, String destinationPath) {

		EmployeeRepositoryImpl e = new EmployeeRepositoryImpl();
		try {

			String tableName = EmployeeTableSQLConstants.Table_Employee.tableName;
			String colNames = e.getColumnNames(tableName);
			String[] colNamesArray = e.getColumnNames(tableName).split(",");
			String[] empIdArray = empId.split(",");
			StringBuffer eiDbuff = new StringBuffer();
			
			for(int i = 0;i < empIdArray.length;i++){
				
				eiDbuff.append("'").append(empIdArray[i]).append("'");
				if(i!=empIdArray.length -1){
					eiDbuff.append(",");
				}
			}
			
			String selQuery = "Select " + colNames + " from " + tableName + " where EmployeeId in ("+eiDbuff.toString()+")";
			System.out.println("selQuery :"+selQuery);
			
			FileWriter file = new FileWriter(destinationPath+"//" + tableName + ".csv");
			BufferedWriter bwr = new BufferedWriter(file);
			PreparedStatement dataStatement = e.conn.prepareStatement(selQuery);
			bwr.write(e.getColumnNames(tableName));
			ResultSet rs = dataStatement.executeQuery();
			while (rs.next()) {
				StringBuffer data = new StringBuffer();
				for (int i = 0; i < colNamesArray.length; i++) {
					data.append("'" + rs.getString(colNamesArray[i]) + "'");
					if (i < colNamesArray.length - 1) {
						data.append(",");
					}
				}
				bwr.write("\n");
				bwr.write(data.toString());
			}
			bwr.flush();
			file.flush();
			bwr.close();
			file.close();

		} catch (Throwable e2) {
			e2.printStackTrace();
		}

	}

	@Override
	public void addUnit(Units units) throws Exception {
		try {
			PreparedStatement preparedStatement = conn
					.prepareStatement(EmployeeTableSQLConstants.createUnit);
			preparedStatement.setString(1, units.getCompanyCode());
			preparedStatement.setString(2, units.getUnitId());
			preparedStatement.setString(3, units.getName());
			preparedStatement.setString(4, units.getAddress1());
			preparedStatement.setString(5, units.getAddress2());
			preparedStatement.setString(6, units.getCity());
			preparedStatement.setString(7, units.getState());
			preparedStatement.setString(8, units.getCountry());
			preparedStatement.setString(9, units.getZipcode());
			preparedStatement.setString(10, units.getTelephone1());
			preparedStatement.setString(11, units.getTelephone2());
			preparedStatement.setString(12, units.getFax());
			preparedStatement.setString(13, units.getMailId());
			PayRollUtility.debug("addUnit : "+preparedStatement.toString());
			try {
				int status = preparedStatement.executeUpdate();
				PayRollUtility.debug("create units status : " + status);
			} catch (MySQLIntegrityConstraintViolationException exception) {
				exception.printStackTrace();
				throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
			} catch (Exception e) {
				e.printStackTrace();
				throw new DatabaseException(0);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public ArrayList<Units> getUnits(String companyCode) throws Exception {
		
		 ArrayList<Units> unitList = new ArrayList<Units>();
		PreparedStatement preparedStatement = conn
				.prepareStatement(EmployeeTableSQLConstants.getAllUnits);
		preparedStatement.setString(1, companyCode);
		PayRollUtility.debug("preparedStatement get units  :"+preparedStatement);
		try {
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				Units units = new Units();
				units.setName(rs.getString(EmployeeTableSQLConstants.Table_Unit.name));
				units.setCompanyCode(rs.getString(EmployeeTableSQLConstants.Table_Unit.companyCode));
				units.setUnitId(rs.getString(EmployeeTableSQLConstants.Table_Unit.unitCode));
				units.setAddress1(rs.getString(EmployeeTableSQLConstants.Table_Unit.address1));
				units.setAddress2(rs.getString(EmployeeTableSQLConstants.Table_Unit.address2));
				units.setCity(rs.getString(EmployeeTableSQLConstants.Table_Unit.city));
				units.setState(rs.getString(EmployeeTableSQLConstants.Table_Unit.state));
				units.setCountry(rs.getString(EmployeeTableSQLConstants.Table_Unit.country));
				units.setZipcode(rs.getString(EmployeeTableSQLConstants.Table_Unit.zipcode));
				units.setTelephone1(rs.getString(EmployeeTableSQLConstants.Table_Unit.telophone1));
				units.setTelephone2(rs.getString(EmployeeTableSQLConstants.Table_Unit.telephone2));
				units.setFax(rs.getString(EmployeeTableSQLConstants.Table_Unit.fax));
				units.setMailId(rs.getString(EmployeeTableSQLConstants.Table_Unit.emalId));
				unitList.add(units);
			}
			return unitList;
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error while getting units information for company  "+companyCode);
		}
	}

	@Override
	public void updateUnit(Units units) throws Exception {
		try {
			
			PreparedStatement preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.updateUnit);
			preparedStatement.setString(1, units.getName());
			preparedStatement.setString(2, units.getAddress1());
			preparedStatement.setString(3, units.getAddress2());
			preparedStatement.setString(4, units.getCity());
			preparedStatement.setString(5, units.getState());
			preparedStatement.setString(6, units.getCountry());
			preparedStatement.setString(7, units.getZipcode());
			preparedStatement.setString(8, units.getTelephone1());
			preparedStatement.setString(9, units.getTelephone2());
			preparedStatement.setString(10, units.getFax());
			preparedStatement.setString(11, units.getMailId());
			preparedStatement.setString(12, units.getUnitId());
			preparedStatement.setString(13, units.getCompanyCode());
			
			PayRollUtility.debug("updateUnit : "+preparedStatement.toString());
			
			try {
				int status = preparedStatement.executeUpdate();
				PayRollUtility.debug("create units status : " + status);
			} catch (MySQLIntegrityConstraintViolationException exception) {
				exception.printStackTrace();
				throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
			} catch (Exception e) {
				e.printStackTrace();
				throw new DatabaseException(0);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public void addBank(BankInfo bankInfo) throws Exception {
		try {
			PreparedStatement preparedStatement = conn
					.prepareStatement(EmployeeTableSQLConstants.createBankInfo);
			preparedStatement.setString(1, bankInfo.getBankCode());
			preparedStatement.setString(2, bankInfo.getName());
			preparedStatement.setString(3, bankInfo.getAddress());
			preparedStatement.setString(4, bankInfo.getCity());
			preparedStatement.setString(5, bankInfo.getState());
			preparedStatement.setString(6, bankInfo.getCountry());
			preparedStatement.setString(7, bankInfo.getZipcode());
			preparedStatement.setString(8, bankInfo.getTelephone1());
			preparedStatement.setString(9, bankInfo.getTelephone2());
			preparedStatement.setString(10, bankInfo.getFax());
			preparedStatement.setString(11, bankInfo.getMailId());
			preparedStatement.setString(12, bankInfo.getISFCCode());
			PayRollUtility.debug("addBank : "+preparedStatement.toString());
			try {
				int status = preparedStatement.executeUpdate();
				PayRollUtility.debug("create banks status : " + status);
			} catch (MySQLIntegrityConstraintViolationException exception) {
				exception.printStackTrace();
				throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
			} catch (Exception e) {
				e.printStackTrace();
				throw new DatabaseException(0);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}

	@Override
	public ArrayList<BankInfo> getBankInfo(String bankCode) throws Exception {
		
		ArrayList<BankInfo> bankInfoList = new ArrayList<BankInfo>();
		
		String query = EmployeeTableSQLConstants.getAllBanksInfo;
		
		if(bankCode != null){
			 query = EmployeeTableSQLConstants.getBankInfoById;
		}
		
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		if(bankCode != null){
		
			preparedStatement.setString(1, bankCode);
		}
		
		
		
		PayRollUtility.debug("preparedStatement get banks Info :"+preparedStatement);
		try {
			ResultSet rs = preparedStatement.executeQuery();
			
			while (rs.next()) {
				BankInfo bankInfo = new BankInfo();
				bankInfo.setName(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.name));
				bankInfo.setBankCode(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.bankCode));
				bankInfo.setAddress(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.address));
				bankInfo.setCity(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.city));
				bankInfo.setState(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.state));
				bankInfo.setCountry(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.country));
				bankInfo.setZipcode(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.zipcode));
				bankInfo.setTelephone1(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.telophone1));
				bankInfo.setTelephone2(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.telephone2));
				bankInfo.setFax(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.fax));
				bankInfo.setMailId(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.emalId));
				bankInfo.setISFCCode(rs.getString(EmployeeTableSQLConstants.Table_BankInfo.ISFCCode));
				bankInfoList.add(bankInfo);
			}
			return bankInfoList;
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error while getting Bank information for bank  "+bankCode);
		}		
	}

	@Override
	public void updateBankInfo(BankInfo bankInfo) throws Exception {
	try {			
			PreparedStatement preparedStatement = conn.prepareStatement(EmployeeTableSQLConstants.updateBankInfo);
			preparedStatement.setString(1, bankInfo.getName());
			preparedStatement.setString(2, bankInfo.getAddress());
			preparedStatement.setString(3, bankInfo.getCity());
			preparedStatement.setString(4, bankInfo.getState());
			preparedStatement.setString(5, bankInfo.getCountry());
			preparedStatement.setString(6, bankInfo.getZipcode());
			preparedStatement.setString(7, bankInfo.getTelephone1());
			preparedStatement.setString(8, bankInfo.getTelephone2());
			preparedStatement.setString(9, bankInfo.getFax());
			preparedStatement.setString(10, bankInfo.getMailId());
			preparedStatement.setString(11, bankInfo.getISFCCode());
			preparedStatement.setString(12, bankInfo.getBankCode());
			
			PayRollUtility.debug("updateBankInfo : "+preparedStatement.toString());
			
			try {
				int status = preparedStatement.executeUpdate();
				PayRollUtility.debug("create banks status : " + status);
			} catch (MySQLIntegrityConstraintViolationException exception) {
				exception.printStackTrace();
				throw new DatabaseException(DatabaseException.DUPLICATE_ENTRY);
			} catch (Exception e) {
				e.printStackTrace();
				throw new DatabaseException(0);
			}
		} catch (DatabaseException e) {
			throw e;
		} catch (Exception e) {
			throw new DatabaseException(0);
		}
	}
	
}
